package DAO;
import java.util.List;

import net.sf.hibernate.Criteria;
import net.sf.hibernate.HibernateException;
import net.sf.hibernate.Query;
import net.sf.hibernate.Session;
import net.sf.hibernate.Transaction;

import Data.IocThongtintiepnhan;
import HibernateUtil.HibernateUtil;

public class ioc_thongtintiepnhanDAO {

	private static Session session;
	public ioc_thongtintiepnhanDAO() {
		try {
			this.session = HibernateUtil.getSessionFactory().openSession();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public boolean save(IocThongtintiepnhan tt){
		boolean check = false;
		Transaction transaction = null;
		try {
			transaction = session.beginTransaction();
			session.save(tt);
			transaction.commit();
			check = true;
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			try {
				if (transaction.wasCommitted()) {
					transaction.rollback();
				}
			} catch (HibernateException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return check;
	}
	public boolean edit(IocThongtintiepnhan tt){
		boolean check = false;
		Transaction transaction = null;
		try {
			transaction = session.beginTransaction();
			session.update(tt);
			transaction.commit();
			check = true;
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			try {
				if (transaction.wasCommitted()) {
					transaction.rollback();
				}
			} catch (HibernateException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return check;
	}
	public boolean delete(IocThongtintiepnhan tt){
		boolean check = false;
		Transaction transaction = null;
		try {
			transaction = session.beginTransaction();
			session.delete(tt);
			transaction.commit();
			check = true;
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			try {
				if (transaction.wasCommitted()) {
					transaction.rollback();
				}
			} catch (HibernateException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		
		return check;
	}
	public IocThongtintiepnhan[] getAllThongTin(){
		Transaction transaction = null;
		IocThongtintiepnhan[] loc = null;
		try {
            transaction = session.beginTransaction();
           /* String sql = "select gid, ma_tinh, ma_huyen, ma_xaa, st_astext(the_geom) as Point, createdDate, createdAccount, ";
            sql+="actived, id, nguoiGui, tenLienhe, emailLienhe, dienthoaiLienhe, ";
            sql+="loaiSukien, trangthaiSukien, phamviSukien, hanChe, tieuDe, ";
            sql+="mucdoNghiemtrong, mucdoChacchan, mucdoKhancap, moTa, ngayGui, ";
            sql+="thoidiemGui, ngayHieuluc, thoidiemHieuluc, ngayBatdau, thoidiemBatdau, ";
            sql+="ngayKetthuc, thoidiemKetthuc, motaKhuvuc, longitude, latitude FROM IocThongtintiepnhan";*/
            
           /* String sql = "SELECT id, gid, ma_tinh, ma_huyen, ma_xa, st_astext(the_geom) as Point, created_date, created_account, ";
            sql+="actived, id, nguoi_gui, ten_lienhe, email_lienhe, dienthoai_lienhe, ";
            sql+="loai_sukien, trangthai_sukien, phamvi_sukien, han_che, tieu_de, ";
            sql+="mucdo_nghiemtrong, mucdo_chacchan, mucdo_khancap, mo_ta, ngay_gui, ";
            sql+="thoidiem_gui, ngay_hieuluc, thoidiem_hieuluc, ngay_batdau, thoidiem_batdau, ";
            sql+="ngay_ketthuc, thoidiem_ketthuc, mota_khuvuc, longitude, latitude";
            sql+="FROM ioc_thongtintiepnhan where id <10";*/
            
/*            Query query = session.createQuery(sql);
            List list = query.list(); */

            //String sql = "select s.id as {s.id} , s.gid as {s.gid} from ioc_thongtintiepnhan {s}";
            
             String sql = "SELECT s.id as {s.id}, s.gid as {s.gid}, s.ma_tinh as {s.maTinh}, s.ma_huyen as {s.maHuyen}, s.ma_xa as {s.maXa}, st_astext(s.the_geom) as {s.theGeom}, s.created_date as {s.createdDate}, s.created_account as {s.createdAccount}, ";
            sql+="s.actived as {s.actived}, s.nguoi_gui as {s.nguoiGui}, s.ten_lienhe as {s.tenLienhe}, s.email_lienhe as {s.emailLienhe}, s.dienthoai_lienhe as {s.dienthoaiLienhe}, ";
            sql+="s.loai_sukien as {s.loaiSukien}, s.trangthai_sukien as {s.trangthaiSukien}, s.phamvi_sukien as {s.phamviSukien}, s.han_che as {s.hanChe}, s.tieu_de as {s.tieuDe}, ";
            sql+="s.mucdo_nghiemtrong as {s.mucdoNghiemtrong}, s.mucdo_chacchan as {s.mucdoChacchan}, s.mucdo_khancap as {s.mucdoKhancap}, s.mo_ta as {s.moTa}, s.ngay_gui as {s.ngayGui}, ";
            sql+="s.thoidiem_gui as {s.thoidiemGui}, s.ngay_hieuluc as {s.ngayHieuluc}, s.thoidiem_hieuluc as {s.thoidiemHieuluc}, s.ngay_batdau as {s.ngayBatdau}, s.thoidiem_batdau as {s.thoidiemBatdau}, ";
            sql+="s.ngay_ketthuc as {s.ngayKetthuc}, s.thoidiem_ketthuc as {s.thoidiemKetthuc}, s.mota_khuvuc as {s.motaKhuvuc}, s.longitude as {s.longitude}, s.latitude as {s.latitude}";
            sql+=" FROM ioc_thongtintiepnhan {s}";
           
            Query query = session.createSQLQuery(sql,new String[] {"s"}, new Class[] {IocThongtintiepnhan.class});
            //Query query = session.crea
            session.createCriteria(IocThongtintiepnhan.class);
            List list = query.list();
            
            loc = new IocThongtintiepnhan[list.size()];
            list.toArray(loc);
            session.flush();
            transaction.commit();
            
        } catch (Exception e) {
//            try {
//				if (transaction.wasCommitted()) {
//				    transaction.rollback();
//				}
            	e.printStackTrace();
//			} catch (HibernateException e1) {
//				// TODO Auto-generated catch block
//				e1.printStackTrace();
//			}
//            e.printStackTrace();
        }
		return loc;
	}
	public IocThongtintiepnhan searchThongTinById(int id){
		IocThongtintiepnhan obj = null;
		Transaction transaction = null;
		try {
            transaction = session.beginTransaction();
            String sql = "from IocThongtintiepnhan where id = ?";
            Query query = session.createQuery(sql);
            query.setInteger(0, id);
            obj = (IocThongtintiepnhan) query.uniqueResult();
            session.flush();
            transaction.commit();
        } catch (Exception e) {
            try {
				if (transaction.wasCommitted()) {
				    transaction.rollback();
				}
			} catch (HibernateException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
            e.printStackTrace();
        }
        return obj;
	}
	
	private int pageSize = 3;
	public IocThongtintiepnhan[] getData(int pageNumber) {
		Transaction transaction = null;
		IocThongtintiepnhan[] loc = null;
		try {
			transaction = session.beginTransaction();
			String sql = "FROM IocThongtintiepnhan";
			Query query = session.createQuery(sql);
			query = query.setFirstResult(pageSize * (pageNumber - 1));
			query.setMaxResults(pageSize);
			List list = query.list(); 
			loc = new IocThongtintiepnhan[list.size()];
			list.toArray(loc);
			session.flush();
			transaction.commit();
	   } catch (Exception e) {
	     e.printStackTrace();
	   }
	   return loc;
	}
	
	public int getDataCount() {
		Transaction transaction = null;
		int count = 0;
		try {
			transaction = session.beginTransaction();
			count = (Integer)session.createQuery("count(*) from IocThongtintiepnhan").uniqueResult();
	   } catch (Exception e) {
	     e.printStackTrace();
	   }
	   return count;
	}
}
